﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using System.Xml.Linq;
using ComURLApi.JinglingOperate;
using ComUtils;
using ComUtils2;
using DevExpress.Utils.DirectXPaint;
using DevExpress.Utils.Svg;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraLayout.Customization;
using DevExpress.XtraPrinting.Native.WebClientUIControl;
using Epon;
using EponFrame;
using ExcelDataReader;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using NLog;
using SQLiteHelper;
using EponMySql = ComUtils.EponMySql;
namespace MySqlSyncSqlServer
{
    public partial class Form1 : Form 
    {
        private static readonly Logger logger = NLog.LogManager.GetCurrentClassLogger();
        IniUtils IniUtils = new IniUtils();
        // mysql 公共链接
        MySqlConnection My_conn;
        bool openflag1 = false;
        SqlConnection SQL_conn= new SqlConnection();
        bool openflag2 = false;
        string connectString_Mysql;
        string connectString_SQL;
        int id_cnn = 0;
        string auto = "";
        private EponMySql EponMySql_cnn = new EponMySql();

        private string departmentid = "337907767316062208";
        private string mysqlcnn_str = "";

        Sqlnet sqlnet_erp ;
        public Form1()
        {
            InitializeComponent();
            auto= IniUtils.ContentReader("AUTO", "FLG", "", Application.StartupPath+"/Cfg.ini");
            departmentid = IniUtils.ContentReader("AUTO", "departmentid", "", Application.StartupPath + "/Cfg.ini");
            id_cnn =Convert.ToInt16(IniUtils.ContentReader("AUTO", "ID", "", Application.StartupPath + "/Cfg.ini"));
        }

        private void 数据链接ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            EponMySql_cnn = new EponMySql();
            EponMySql_cnn.conn = new MySqlConnection();
            if (!openflag1)
            {
                ///处理mysql 
               GetConnection_MySQL(id_cnn);
                ///处理sql server 
               GetConnection_MSSQL(id_cnn);
            }
            else
            {
                //处理mysql 
                My_conn.Close();
                openflag1 = false;
                Uplog("MySQL 数据库已关闭");
                ///处理sql server 
                SQL_conn.Close();
                openflag2 = false;
                Uplog("SQL 数据库已关闭");
            }
            
        }
        public long logint = 0;
        public long logint2 = 0;
        public long logint3 = 0;
        /// <summary>
        /// 输出日志
        /// </summary>
        /// <param name="log"></param>
        private void Uplog(string log)
        {

            if (logint > 40)
            {

                logView.Clear();
                logint = 0;
            }
            logger.Info(log);
            logView.AppendText(logint + ":" + log + "\r\n");
            if (log.Length < 30)
            {
                tssl_log.Text = log;
            }

            logint = logint + 1;
        }

        private void xunhuangongzuo1()
        {
            if (SQL_conn == null)
            {
                GetConnection_MSSQL(id_cnn);
            }
            if (My_conn == null)
            {
                GetConnection_MySQL(id_cnn);
            }
            Console.WriteLine(SQL_conn.State.ToString()+"//"+ My_conn.State.ToString());
            if (SQL_conn.State == ConnectionState.Open && My_conn.State == ConnectionState.Open)
            {

                //查询View_PLM_ITEM
               string Mssql_Xh = "" +
                "SELECT TOP 1000 [danwei]\n" +
                "      ,[FNumber]\n" +
                "      ,[FName]\n" +
                "      ,[FModel]\n" +
                "      ,[FItemClassID]\n" +
                "      ,[FExternID]\n" +
                "      ,[FParentID]\n" +
                "      ,[FLevel]\n" +
                "      ,[FDetail]\n" +
                "      ,[isSync]\n" +
                "      ,[FDeleted]\n" +
                "      ,[FHelpCode]\n" +
                "      ,[FBrNo]\n" +
                "      ,[FUnUsed]\n" +
                "      ,[FFullNumber]\n" +
                "      ,[FDiff]\n" +
                "      ,[Expr1]\n" +
                "      ,[FShortNumber]\n" +
                "      ,[FFullName]\n" +
                "      ,[UUID]\n" +
                "      ,[FGRCommonID]\n" +
                "      ,[FSystemType]\n" +
                "      ,[FUseSign]\n" +
                "      ,[FChkUserID]\n" +
                "      ,[FAccessory]\n" +
                "      ,[FGrControl]\n" +
                "      ,[FModifyTime]\n" +
                "      ,[FHavePicture]\n" +
                "  FROM [AIS20171206082230].[dbo].[View_PLM_ITEM] where FLevel=4 and isSync=0 ";

        DataTable ordertable = query(SQL_conn, Mssql_Xh);
                if(ordertable.Rows.Count>0)
                {
                    insertMaterialCode_PLM(My_conn, ordertable, SQL_conn);
                }
                
                Uplog( "查询数据成功");

            }
            else
            {
                Uplog("数据库链接未开启");
            }
        }



        private void xinhengK3ErpToPlm()
        {
            if (SQL_conn == null)
            {
                GetConnection_MSSQL(id_cnn);
            }
            if (My_conn == null)
            {
                GetConnection_MySQL(id_cnn);
            }
             
                // 1、查询表 OITM_PLM
                string sqlcmd1 = "select itemcode,validComm,itemName,ItmsgrpNam,U_GXXL,InvntryUom,u_jdbm,u_bb,name,u_ywy,frozenfor,flag,flag1 from OITM_PLM where flag='N'and flag1='N'";
                DataTable dt = query(SQL_conn, sqlcmd1); 
                //SQL_conn.Close();
                //Uplog("关闭sqlserver数据连接");
                //2、往PLM系统中插入数据
                Boolean ff = insertOITM_PLM(My_conn, dt, SQL_conn);
                //查询需要更新的数据(sqlserver)
                string sqlcmd2 = "select itemcode,validComm,itemName,ItmsgrpNam,U_GXXL,InvntryUom,u_jdbm,u_bb,name,u_ywy,frozenfor,flag,flag1 from OITM_PLM where flag='Y' and flag1='N'";
                DataTable dt1 = query(SQL_conn, sqlcmd2);
                //更新
                if (dt1.Rows.Count > 0)

                {

                    UpdateMysql(dt1, My_conn, SQL_conn);
                }
                //查询订单表
                string order = @" 

                    select q1. [OrderID]as ID,
                    q1.[CustOrderNo]  as docentry ,--  docEntry订单号
                     q1.[OrderDate] as docdate,--docDate 下单日期
                    q1.[CustomerNo]as cardcode, --cardCode客户编码
                    q2.Customer as cardname,--cardName客户名称
                    currency as currency, --Currency 币别
                    q1.CreateMan as  itemcode, --itemCode ERP编码
                     [OrderName]as u_oitem, --u_Oitem 项目名称
                     -- subcatnum, 
                    (UrgentGrade +'_'+q1.OrderType) as quantity, --quantity 数量

                    '' as shipdate    --shipDate交货日期
                    FROM [ZHJX].[dbo].[TbSaleOrderInfo]  as q1 inner    join  [dbo].[TbCustomer] q2  on  q1.CustomerNo=q2.CustNo
                    where q1.ExtField4 is null ";
                DataTable ordertable = query(SQL_conn, order);
                InsertORDR_PLM(My_conn, ordertable, SQL_conn);
                string orderUpdate = "select docentry, docdate, cardcode, cardname, currency, itemcode, u_oitem, subcatnum, quantity, shipdate, u_dycs from ORDR_PLM where flag = 'Y' and flag1 = 'N'";
                DataTable orderTable2 = query(SQL_conn, orderUpdate);
                if (orderTable2.Rows.Count > 0)
                {
                    UpdateORDR_PLM(orderTable2, My_conn, SQL_conn);
                }

                //操作OITT_PLM 表
                string OITT = "select father,childnum,code,quantity,warehouse,issuemthd,flag,flag1 from OITT_PLM where flag='N' and flag1='N'";
                DataTable oitttable = query(SQL_conn, OITT);
                InsertOITT_PLM(My_conn, oitttable, SQL_conn);
                //查询新修改的数据
                string oitt_update = "select father,childnum,code,quantity,warehouse,issuemthd,flag,flag1 from OITT_PLM where flag='Y' and flag1='N'";
                DataTable oittupdate = query(SQL_conn, oitt_update);
                if (oittupdate.Rows.Count > 0)
                {
                    UpdateOITT_PLM(oittupdate, My_conn, SQL_conn);
                }
                Uplog("查询数据成功");

           
        }
      

        public void GetConnection_MSSQL(int id)
        {
            if (id ==0)
            {
                connectString_SQL = $"Data Source= '{MSserver}';Initial Catalog='{MSdatabase}';User ID='{MSuid}';Password='{MSpwd}';";
                Uplog(connectString_SQL); 
                 sqlnet_erp = new Sqlnet( MSserver,MSuid,MSpwd,MSdatabase); 
            }

            if (id == 1)
            {
                connectString_SQL = "Data Source= 'WIN-LIKAAG0T4R1';Initial Catalog='AIS20171206082230';User ID='kd';Password='kingdee';";
                Uplog("Data Source= 'WIN-LIKAAG0T4R1';Initial Catalog='AIS20171206082230'");
            }
            if (id == 2)
            {
                connectString_SQL = "Data Source= '127.0.0.1';Initial Catalog='AIS20171206082230';User ID='sa';Password='swf@epon';";
                Uplog("2222");
            }
            if (id == 5)
            {
                connectString_SQL = "Data Source= '192.168.88.3';Initial Catalog='AIS20211223170448';User ID='sa';Password='KY.ky85502635/*-+';";
                Uplog("Data Source= '192.168.88.3';Initial Catalog='AIS20211223170448'");

                sqlnet_erp = new Sqlnet(MSserver, MSuid, MSpwd, MSdatabase);

            }
            // server=127.0.0.1/localhost 代表本机，端口号port默认是3306可以不写
            SQL_conn = new SqlConnection(connectString_SQL);
            try
            {
                SQL_conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句
                
                Uplog("MSSQL 连接正常！" );
                openflag2 = true; 
            }
            catch (SqlException ex)
            {
                switch (ex.Number)
                {
                    case -2:
                        Uplog("SQL timeout！");
                        break;
                    case 17:
                        Uplog("SQL server 不存在 或者拒绝访问");
                        break;
                    case 17142:
                        Uplog("SQL server 暂停");
                        break;
                    case 2627:
                        Uplog("SQL 主键重复");
                        break;
                    case 2812:
                        Uplog("SQL 存储过程没有");
                        break;
                    case 208:
                        Uplog("SQL 表不存在");
                        break;
                }
            }
            finally
            {
                //conn.Close();
            }
        }

        public void GetConnection_MySQL(int id)
        {
            // connectString_Mysql = "server='192.168.1.120';port='3306';user='root';password='DSWzmm01'; database='plmpj';Character Set='utf8'";
            if (id ==0)
            {
                EponMySql_cnn._mysql_database = mysql_database;
                EponMySql_cnn._mysql_server = mysql_server;
                EponMySql_cnn._mysql_port = mysql_port;
                EponMySql_cnn._mysql_pwd = mysql_pwd;
                EponMySql_cnn._mysql_uid = mysql_uid;

                EponMySql_cnn.conn.ConnectionString = mysqlcnn_str;
                EponMySql_cnn.getconn();
            }
            
            if (id == 1)
            {
                departmentid = "337907767316062208";
                connectString_Mysql = "server='192.168.1.247';port='3306';user='root';password='plmmysql'; database='plmpj';Character Set='utf8'";
                EponMySql_cnn.conn.ConnectionString = connectString_Mysql;
                EponMySql_cnn.getconn();
            }
            if (id == 2)
            {
                 //xinheng bumen ---hauweiyun202890544441466880
                        departmentid = "202890544441466880";
                    connectString_Mysql = "server='121.37.26.202';port='3306';user='root';password='Gzypkj@123456plmmysql'; database='plmpj';Character Set='utf8'";
                EponMySql_cnn.conn = new MySqlConnection();
                EponMySql_cnn.conn.ConnectionString = connectString_Mysql;
            }
            if (id == 3)
            {
                //xinheng bumen ---hauweiyun202890544441466880
                departmentid = "723920806441656320";
                connectString_Mysql = "server='192.168.120.244';port='3306';user='root';password='plmmysql'; database='plmpj';Character Set='utf8'";
                EponMySql_cnn.conn = new MySqlConnection();
                EponMySql_cnn.conn.ConnectionString = connectString_Mysql;
            }
            if (id == 4)
            {
                //广东时纬
                departmentid = "744495185206452224";
                connectString_Mysql = "server='192.168.1.118';port='3306';user='root';password='Plmmysql57!'; database='plmpj';Character Set='utf8'";
                EponMySql_cnn.conn.ConnectionString = connectString_Mysql;
                EponMySql_cnn.getconn();
            }

            if (id == 5)
            {
                //佛山凯洋
                EponMySql_cnn._mysql_database = mysql_database;
                EponMySql_cnn._mysql_server = mysql_server;
                EponMySql_cnn._mysql_port = mysql_port;
                EponMySql_cnn._mysql_pwd = mysql_pwd;
                EponMySql_cnn._mysql_uid = mysql_uid; 
                EponMySql_cnn.conn.ConnectionString = mysqlcnn_str;
                EponMySql_cnn.getconn();
            }


            // server=127.0.0.1/localhost 代表本机，端口号port默认是3306可以不写
            My_conn = new MySqlConnection(mysqlcnn_str);
            try
            {
                My_conn.Open();//打开通道，建立连接，可能出现异常,使用try catch语句

                Uplog("MySQL 连接正常！");
                openflag1 = true;
                //Console.WriteLine("连接正常！！！！");
                //在这里使用代码对数据库进行增删查改
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("MySQL 不能连接服务器！");
                        break;
                    case 1045:
                        MessageBox.Show("MySQL 账号密码错误！");
                        break;
                    case 1046:
                        MessageBox.Show("MySQL 数据库未选择");
                        break;
                     
                }
            }
            finally
            {
                //conn.Close();
            }
        }
        public long ProcessName(string ProceName)
        {
            //System.Diagnostics.Process[] process1 = Process.GetProcesses();
            System.Diagnostics.Process[] processes = Process.GetProcesses();
            long m1 = 0;
            long m2 = 0;
            long m3 = 0;
            foreach (Process pres in processes)
            {
                if (pres.ProcessName == ProceName)
                {
                    Uplog((pres.ProcessName));
                    //获取bai指定进程du内存使用情况
                    m1 = (pres.PrivateMemorySize64 / 1048576);
                    Uplog("PrivateMemorySize64内存大小1：" + m1.ToString() + "Mb");

                    //获取指定进程分配的物理内zhi存量
                    m2 = (pres.WorkingSet64 / 1048576);
                    Uplog("WorkingSet64内存大小2：" + m2.ToString() + "Mb");
                    m3 = (pres.PagedSystemMemorySize64 / 1048576);
                    Uplog("PagedSystemMemorySize64内存大小3：" + m3.ToString() + "Mb"); 
                    if (m1 < m2)
                    {
                        m1 = m2;
                    }
                    if (m1 < m3)
                    {
                        m1 = m3;
                    }
                    break; 
                } 
            }
            return m1; 
        }

        public DataTable query(SqlConnection conn, string sql)
        {
            //server=127.0.0.1;
            DataTable dt = new DataTable();
            //DataRow dr;
            //string connString = "server=127.0.0.1;uid=sa;pwd=;database=TJPTTC";
            //SqlConnection conn = new SqlConnection(connString);
            SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
            adapter.Fill(dt);
            return dt;
        }
        //SQL非查询语句
        public void noQuery(SqlConnection conn, string sql)
        {
            //server=127.0.0.1;
            //string connString = "server=GLASSESONION;uid=sa;pwd=;database=TJPTTC";
            //SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        /// <summary>
        /// 同步新插入数据的方法
        /// </summary>
        /// <param name="My_conn"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public Boolean insertOITM_PLM(MySqlConnection My_conn, DataTable table, SqlConnection conn)
        {
            Boolean falg = false;
            string itemcode;
            // string itemcode_tuf_8 = Encoding.UTF8.GetString(Encoding.Default.GetBytes(itemcode));
            string validComm;
            string itemName;
            string ItmsgrpNam;
            string U_GXXL;
            string InvntryUom;
            string u_jdbm;
            string u_bb;
            string name;
            string u_ywy;
            string frozenFor;
            string Sync_flg = "0";
            //select itemcode,validComm,itemName,ItmsgrpNam,U_GXXL,InvntryUom,u_jdbm,u_bb,name,u_ywy from  View_OITM1800_PLM       
            for (int i = 0; i < table.Rows.Count; i++)
            {
                itemcode = table.Rows[i]["itemcode"].ToString();
                validComm = table.Rows[i]["validComm"].ToString();
                itemName = table.Rows[i]["itemName"].ToString();
                ItmsgrpNam = table.Rows[i]["ItmsgrpNam"].ToString();
                U_GXXL = table.Rows[i]["U_GXXL"].ToString();
                InvntryUom = table.Rows[i]["InvntryUom"].ToString();
                u_jdbm = table.Rows[i]["u_jdbm"].ToString();
                u_bb = table.Rows[i]["u_bb"].ToString();
                name = table.Rows[i]["name"].ToString();
                u_ywy = table.Rows[i]["u_ywy"].ToString();
                frozenFor = table.Rows[i]["frozenFor"].ToString();
                string insertplm = string.Format("INSERT INTO OITM_PLM(ItemCode, ValidComm, ItemName, ItmsGrpNam, U_GXXL, InvntryUom, U_JDBM, U_bb, Name, U_YWY, frozenFor)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", itemcode, validComm, itemName, ItmsgrpNam, U_GXXL, InvntryUom, u_jdbm, u_bb, name, u_ywy, frozenFor);
                MySqlCommand my = new MySqlCommand(insertplm, My_conn);
                my.ExecuteNonQuery();
                string updateSAP = "update  OITM_PLM set flag1='Y' where itemcode='" + itemcode + "'";
                SqlCommand my1 = new SqlCommand(updateSAP, conn);
                my1.ExecuteNonQuery();
                Write.WriteLog(table.Rows[i]["code"].ToString() + "在insertOITM_PLM已同步", "SQLDataSync");
            } 
            falg = true; 
            return falg;
        }
        /// <summary>
        /// 数据插入到Material
        /// </summary>
        /// <param name="My_conn"></param>
        /// <param name="table"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public bool insertMaterialCode_PLM(MySqlConnection My_conn, DataTable table, SqlConnection conn)
        { 
            bool falg = false;
            Uplog("insertXH_PLM_Material执行完毕！-->");
         
            for (int i = 0; i < table.Rows.Count; i++)
            {  
                // 物料编码
                string FNumber = table.Rows[i]["FNumber"].ToString();
                // 名称
                string Fname =   table.Rows[i]["Fname"].ToString(); 
                string FItemID = table.Rows[i]["danwei"].ToString();    
                // 规格
                string FModel = table.Rows[i]["FModel"].ToString();
                string figureNum = "";
                string firstcode = "A";
                string[] olddrawingNO = FModel.Split('/');
                if (FNumber.Length == 18)
                {
                    string[] code01 = FNumber.Split('.');
                    firstcode = code01[0].ToString();
                    if (olddrawingNO.Length > 1)
                    {
                        if (firstcode==("B"))
                        {
                            figureNum = olddrawingNO[0].ToString();
                        }
                    }
                } 
                if (FNumber != null)
                {
                    SonwID sf = new SonwID(0);
                   
                    string id = (sf.nextId()).ToString();
                    //组合SQL语句，将数据插入到编码分类下，category=1
                    string insertXinhengSql = string.Format("INSERT INTO `plmpj`.`material`" +
                            "(`id`,`codeRuleID`, `category`, `materialCode`, `name`, `figureNum`, `departmentId`, `specification`, `status`) " +
                            "VALUES ('{0}', '202207212358','1', '{1}', '{2}', '{4}', '337907767316062208', '{3}', '3');", id, FNumber, Fname, FModel, figureNum);
                    
                    if (NQueryMySql(insertXinhengSql, My_conn))
                    {
                        string update_item = string.Format("update  [AIS20171206082230].[dbo].[t_Item] set [isSync]=1 where [danwei]='{0}' ;", FItemID);
                        NQueryMSSql(update_item, SQL_conn);
                        string str2 = "insertXH_PLM_Material！-->" + FNumber + "-->" + Fname + "-->" + FModel + "-->" + FItemID + "-->" + "成功！" ;
                        Uplog(str2);
                        Write.WriteLog(str2);
                    }
                    else
                    {
                        string str1 = "insertXH_PLM_Material！-->" + FNumber + "-->" + Fname + "-->" + FModel + "-->" + FItemID + "-->" + "失败！";
                        Uplog(str1);
                        Write.WriteLog(str1);
                    }
                }
              
                 
            }
            falg = true;
            return falg;
        }

        private bool NQueryMSSql(string update_item, SqlConnection ms_conn)
        {
            bool falg = false;
            try
            {
                SqlCommand my1 = new SqlCommand(update_item, ms_conn);
                my1.ExecuteNonQuery();
                falg = true;
            }
            catch (Exception)
            {

                throw;
            }


            return falg;
        }

        private bool NQueryMySql(string insertXinhengSql, MySqlConnection my_conn)
        {
            bool falg = false;
            try
            {
                MySqlCommand my = new MySqlCommand(insertXinhengSql, my_conn);
                my.ExecuteNonQuery();
                falg= true;
            }
            catch (Exception)
            { 

            }
        

            return falg;
        }

        /// <summary>
        /// 插入订单表数据的方法
        /// </summary>
        /// <param name="My_conn"></param>
        /// <param name="table"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public Boolean InsertORDR_PLM(MySqlConnection My_conn, DataTable table, SqlConnection conn)
        {
            // select docentry, docdate, cardcode, cardname, currency, itemcode, u_oitem, subcatnum, quantity, shipdate, u_dycs from ORDR_PLM where flag = 'N' and flag1 = 'N'
            Boolean falg = false;
            string docentry;
            // string itemcode_tuf_8 = Encoding.UTF8.GetString(Encoding.Default.GetBytes(itemcode));
            string docdate;
            string code = string.Empty;
            string cardcode;
            string cardname;
            string currency;
            string itemcode;
            string u_oitem;
            string subcatnum;
            string quantity;
            string shipdate;
            string u_dycs;
            string ID = string.Empty;
            //select itemcode,validComm,itemName,ItmsgrpNam,U_GXXL,InvntryUom,u_jdbm,u_bb,name,u_ywy from  View_OITM1800_PLM       
            for (int i = 0; i < table.Rows.Count; i++)
            {
             
                docentry = table.Rows[i]["docentry"].ToString();
                docdate = table.Rows[i]["docdate"].ToString();
                if (docdate == "")  
                {
                    docdate = "2022-03-09 00:00:00";
                }
                cardcode = table.Rows[i]["cardcode"].ToString();
                cardname = table.Rows[i]["cardname"].ToString();
                itemcode = table.Rows[i]["itemcode"].ToString();
                currency = table.Rows[i]["currency"].ToString();
                u_oitem = table.Rows[i]["u_oitem"].ToString();
                subcatnum = table.Rows[i]["subcatnum"].ToString();
                quantity = table.Rows[i]["quantity"].ToString();
                shipdate = table.Rows[i]["shipdate"].ToString();
               // u_dycs = table.Rows[i]["u_dycs"].ToString();
                //sync_time = table.Rows[i]["sync_time"].ToString();
                //string sqlinsert =string.Format("INSERT INTO OITM1800_PLM(ItemCode,ValidComm ,ItemName,ItmsGrpNam,U_GXXL,InvntryUom,U_JDBM,U_bb,Name,U_YWY,Sync_flg)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", itemcode, validComm, itemName, ItmsgrpNam, U_GXXL, InvntryUom, u_jdbm, u_bb, name, u_ywy, frozenFor);
                string insertplm = string.Format("insert into ORDR_PLM(docentry,docdate,cardcode,cardname,currency,itemcode,u_oitem,subcatnum,quantity,shipdate )values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
                    docentry, docdate, cardcode, cardname, itemcode, currency, u_oitem, subcatnum, quantity, shipdate);
                MySqlCommand my = new MySqlCommand(insertplm, My_conn);
                my.ExecuteNonQuery(); 
                string updateSAP = " update cc set ExtField4='ok' FROM [ZHJX].[dbo].[TbSaleOrderInfo]   cc  where cc.[OrderID]='" + ID + "'";
                SqlCommand my1 = new SqlCommand(updateSAP, conn);
                my1.ExecuteNonQuery(); 
                Uplog(docentry.ToString() + "在 InsertORDR_PLM已同步");
                Write.WriteLog(docentry.ToString() + "在 InsertORDR_PLM已同步", "SQLDataSync");
            }
            falg = true;
            return falg;
        }
        public Boolean InsertOITT_PLM(MySqlConnection My_conn, DataTable table, SqlConnection conn)
        {
            // select docentry, docdate, cardcode, cardname, currency, itemcode, u_oitem, subcatnum, quantity, shipdate, u_dycs from ORDR_PLM where flag = 'N' and flag1 = 'N'
            // select father,childnum,code,quantity,warehouse,issuemthd,flag,flag1 from OITT_PLM
            Boolean falg = false;
            string father;
            string childnum;
            string code;
            string quantity;
            string warehouse;
            string issuemthd;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                father = table.Rows[i]["father"].ToString();
                childnum = table.Rows[i]["childnum"].ToString();
                code = table.Rows[i]["code"].ToString();
                quantity = table.Rows[i]["quantity"].ToString();
                warehouse = table.Rows[i]["warehouse"].ToString();
                issuemthd = table.Rows[i]["issuemthd"].ToString();
                
                string insertplm = string.Format("insert into OITT_PLM(father,childnum,code,quantity,warehouse,issuemthd)values('{0}','{1}','{2}','{3}','{4}','{5}')",
                    father, childnum, code, quantity, warehouse, issuemthd);
                MySqlCommand my = new MySqlCommand(insertplm, My_conn);
                my.ExecuteNonQuery();
                string updateSAP = "update  OITT_PLM set flag1='Y' where father='" + father + "'";
                SqlCommand my1 = new SqlCommand(updateSAP, conn);
                my1.ExecuteNonQuery();
                Write.WriteLog(code.ToString() + "在InsertOITT_PLM已同步", "SQLDataSync");
            }
            falg = true;
            return falg;
        }
        public void UpdateMysql(DataTable table, MySqlConnection myconn, SqlConnection sqlconn)
        {
            string sql;
            string sql1;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                sql = string.Format("update OITM_PLM set validComm = '{0}', itemName = '{1}', ItmsgrpNam = '{2}', U_GXXL = '{3}', InvntryUom = '{4}', u_jdbm = '{5}', u_bb = '{6}', name = '{7}', u_ywy = '{8}', frozenfor = '{9}' where itemcode = '{10}'",
                    table.Rows[i]["validComm"].ToString(), table.Rows[i]["itemName"].ToString(), table.Rows[i]["ItmsgrpNam"].ToString(), table.Rows[i]["U_GXXL"].ToString(), table.Rows[i]["InvntryUom"].ToString(), table.Rows[i]["u_jdbm"].ToString(), table.Rows[i]["u_bb"].ToString(), table.Rows[i]["name"].ToString(), table.Rows[i]["u_ywy"].ToString(), table.Rows[i]["frozenfor"].ToString(), table.Rows[i]["itemcode"].ToString());
                myconn.Open();
                MySqlCommand my = new MySqlCommand(sql, myconn);
                my.ExecuteNonQuery();
                sql1 = "update OITM_PLM set flag1 = 'Y' where itemcode = " + table.Rows[i]["itemcode"].ToString() + "";
                SqlCommand sq = new SqlCommand(sql1, sqlconn);
                sq.ExecuteNonQuery();
                Write.WriteLog(table.Rows[i]["code"].ToString() + "在UpdateMysql已同步", "SQLDataSync");
            }
        }
        /// <summary>
        /// 修改订单的表
        /// </summary>
        /// <param name="table"></param>
        public void UpdateORDR_PLM(DataTable table, MySqlConnection myconn, SqlConnection sqlconn)
        {
            string sql;
            string sql1;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                sql = string.Format("update OITM_PLM set docdate = '{0}', cardcode = '{1}', cardname = '{2}', currency = '{3}', itemcode = '{4}', u_oitem = '{5}', subcatnum = '{6}', quantity = '{7}', shipdate = '{8}', u_dycs = '{9}' where docentry = '{10}'",
                    table.Rows[i]["docdate"].ToString(), table.Rows[i]["cardname"].ToString(), table.Rows[i]["currency"].ToString(), table.Rows[i]["itemcode"].ToString(), table.Rows[i]["u_oitem"].ToString(), table.Rows[i]["subcatnum"].ToString(), table.Rows[i]["quantity"].ToString(), table.Rows[i]["shipdate"].ToString(), table.Rows[i]["u_dycs"].ToString(), table.Rows[i]["docentry"].ToString(), table.Rows[i]["docentry"].ToString());
                myconn.Open();
                MySqlCommand my = new MySqlCommand(sql, myconn);
                my.ExecuteNonQuery();
                sql1 = "update ORDR_PLM set flag1 = 'Y' where docentry = " + table.Rows[i]["docentry"].ToString() + "";
                SqlCommand sq = new SqlCommand(sql1, sqlconn);
                sq.ExecuteNonQuery();
                Write.WriteLog(table.Rows[i]["code"].ToString() + "在UpdateORDR_PLM已同步", "SQLDataSync");
            }
        }
        /// <summary>
        /// OITT_PLM表操作
        /// </summary>
        /// <param name="table"></param>
        /// <param name="myconn"></param>
        /// <param name="sqlconn"></param>
        public void UpdateOITT_PLM(DataTable table, MySqlConnection myconn, SqlConnection sqlconn)
        {
            // select father,childnum,code,quantity,warehouse,issuemthd,flag,flag1 from OITT_PLM
            string sql;
            string sql1;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                sql = string.Format("update OITT_PLM set childnum = '{0}', code = '{1}', quantity = '{2}', warehouse = '{3}', issuemthd = '{4}' where father = '{5}'",
                    table.Rows[i]["childnum"].ToString(), table.Rows[i]["code"].ToString(), table.Rows[i]["quantity"].ToString(), table.Rows[i]["warehouse"].ToString(), table.Rows[i]["issuemthd"].ToString(), table.Rows[i]["father"].ToString());
                myconn.Open();
                MySqlCommand my = new MySqlCommand(sql, myconn);
                my.ExecuteNonQuery();
                sql1 = "update OITT_PLM set flag1 = 'Y' where father = '" + table.Rows[i]["father"].ToString() + "'";
                SqlCommand sq = new SqlCommand(sql1, sqlconn);
                sq.ExecuteNonQuery();
                Write.WriteLog(table.Rows[i]["code"].ToString() + "在 UpdateOITT_PLM已同步", "SQLDataSync");
            }
        }


        private void BT_start_Click(object sender, EventArgs e)
        {
            timer1.Interval = 5000;
            timer1.Start();
            Uplog("服务开始...");

        }

        private void 停止服务ToolStripMenuItem_Click(object sender, EventArgs e)
        {

            timer1.Stop();
            Uplog("服务停止...");
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            xinhengchushihua();
            // xunhuangongzuo1();
            logint3 += 1;
            Uplog("循环次数" + logint3);
            logint2 = ProcessName("MySqlSyncSqlServer");
            Uplog("当前程序内存占用：" + logint2);
            Write.WriteLog("循环次数:" + logint3 + ";  " + "当前程序内存占用：" + logint2 + "MB", "SQLDataSync");
            if (logint2 > 200)
            {
                Application.Exit();
                System.Diagnostics.Process.Start(System.Reflection.Assembly.GetExecutingAssembly().Location);
            }
        }
        private string PlmMySQLcfgPath = Path.Combine("C:\\Users\\Public\\Epon\\PLM助手", "plmCfg.ini");

      

        string MSserver =string.Empty;
        string MSuid = string.Empty;
        string MSpwd = string.Empty;
        string MSdatabase = string.Empty;

        string mysql_server = string.Empty;
        string mysql_database = string.Empty;
        string mysql_uid = string.Empty;
        string mysql_pwd = string.Empty;
        string mysql_port = string.Empty;

        private void Form1_Load(object sender, EventArgs e)
        { 
            IniUtils ini = new IniUtils();

            if (new FileInfo(PlmMySQLcfgPath).Exists)
            {
                mysql_server = ini.ContentReader("LOGIN", "MYserver", "", PlmMySQLcfgPath);
                mysql_database = ini.ContentReader("LOGIN", "MYdatabase", "", PlmMySQLcfgPath);
                mysql_uid = ini.ContentReader("LOGIN", "MYuid", "", PlmMySQLcfgPath);
                mysql_pwd = ini.ContentReader("LOGIN", "MYpwd", "", PlmMySQLcfgPath);
                mysql_port = ini.ContentReader("LOGIN", "MYport", "", PlmMySQLcfgPath);
                mysqlcnn_str = $"server={mysql_server};database={mysql_database};uid={mysql_uid};password={mysql_pwd};Port={mysql_port};CharSet=utf8mb4;"; ;
                
                MSserver = ini.ContentReader("LOGIN", "MSserver", "", PlmMySQLcfgPath);
                MSuid = ini.ContentReader("LOGIN", "MSuid", "", PlmMySQLcfgPath);
                MSpwd = ini.ContentReader("LOGIN", "MSpwd", "", PlmMySQLcfgPath);
                MSdatabase = ini.ContentReader("LOGIN", "MSdatabase", "", PlmMySQLcfgPath); 
            }  
        }

        private void 初始化ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            timer2.Interval = 5000;
            timer2.Start();
            Uplog("chushihua服务开始...");
           
        }

        private void xinhengchushihua()
        {
            Uplog("初始化...");
            if (SQL_conn == null)
            {
                GetConnection_MSSQL(id_cnn);
                Uplog("MSSQL初始化...");
            }
            if (My_conn == null)
            {
                GetConnection_MySQL(id_cnn); 
                Uplog("MySQL初始化...");
            }

            Console.WriteLine(SQL_conn.State.ToString() + "//" + My_conn.State.ToString());
            
                //查询View_PLM_ITEM
                string Mssql_Xh =  "SELECT   top 100  *  FROM[AIS20171206082230].[dbo].[View_PLM_ITEM]  where FLevel = 4  and isSync = 1  ";  
                DataTable ordertable = query(SQL_conn, Mssql_Xh);
                if (ordertable.Rows.Count > 0)
                {
                    Uplog("查询数据"+ ordertable.Rows.Count +"行");
                    update_PLMcode(My_conn, ordertable, SQL_conn); 
                } 
                Uplog("查询数据成功"); 
        }
        private void xinhengPlmToErp()
        {
            Uplog("初始化...");
            if (SQL_conn == null)
            {
                GetConnection_MSSQL(id_cnn);
                Uplog("MSSQL初始化...");
            }
            if (My_conn == null)
            {
                GetConnection_MySQL(id_cnn);
                Uplog("MySQL初始化...");
            } 
            Console.WriteLine(SQL_conn.State.ToString() + "//" + My_conn.State.ToString()); 
            string plmCode = "SELECT * FROM `plmpj`.`material`  where FLevel = 4  and isSync = 1 LIMIT 0,1000 ";

            DataTable ordertable = EponMySql_cnn.GetDataTable(plmCode);
            if (ordertable.Rows.Count > 0)
            {
                Uplog("查询数据" + ordertable.Rows.Count + "行");
                update_PLMcode(My_conn, ordertable, SQL_conn);


            }

            Uplog("查询数据成功");


        }
        private bool  update_PLMcode(MySqlConnection my_conn, DataTable table, SqlConnection sQL_conn)
        {
            bool falg = false;
            Uplog("开始执行初始化！-->");

            for (int i = 0; i < table.Rows.Count; i++)
            {

               
                // 物料编码
                string FNumber = table.Rows[i]["FNumber"].ToString();
                // 名称
                string Fname = table.Rows[i]["Fname"].ToString();

                string FItemID = table.Rows[i]["danwei"].ToString();

                // 规格
                string FModel = table.Rows[i]["FModel"].ToString();
                string figureNum = "";
                string firstcode = "A";
                string[] olddrawingNO = FModel.Split('/');
                if (FNumber.Length == 18)
                {
                    string[] code01 = FNumber.Split('.');
                    firstcode = code01[0].ToString();
                    if (olddrawingNO.Length > 1)
                    {
                        if (firstcode == ("B"))
                        {
                            figureNum = olddrawingNO[0].ToString();
                        }
                    }
                }
                if (!existInPlm(FNumber))
                {
                    if (FNumber != null)
                    {
                        SonwID sf = new  SonwID(0);

                        string id = (sf.nextId()).ToString();
                        //组合SQL语句，将数据插入到编码分类下，category=1
                        string insertXinhengSql = string.Format("INSERT INTO `plmpj`.`material`" +
                                "(`id`,`codeRuleID`, `category`, `materialCode`, `name`, `figureNum`, `departmentId`, `specification`, `status`) " +
                                "VALUES ('{0}', '202207212358','1', '{1}', '{2}', '{4}', '337907767316062208', '{3}', '3');", id, FNumber, Fname, FModel.Replace("'C", "°C"), figureNum);

                        if (NQueryMySql(insertXinhengSql, My_conn))
                        {
                            string update_item = string.Format("update  [AIS20171206082230].[dbo].[t_Item] set [isSync]=2 where [danwei]='{0}' ;", FItemID);
                            NQueryMSSql(update_item, SQL_conn);
                            string str2 = "insertXH_PLM_Material！-->" + FNumber + "-->" + Fname + "-->" + FModel + "-->" + FItemID + "-->" + "成功！";
                            Uplog(str2);
                            Write.WriteLog(str2);
                        }
                        else
                        {
                            string update_item = string.Format("update  [AIS20171206082230].[dbo].[t_Item] set [isSync]=3 where [danwei]='{0}' ;", FItemID);
                            NQueryMSSql(update_item, SQL_conn); 
                            string str1 = "insertXH_PLM_Material！-->" + FNumber + "-->" + Fname + "-->" + FModel + "-->" + FItemID + "-->" + "失败！";
                            Uplog(str1); 
                            Write.WriteLog(str1);
                            Write.WriteLog(insertXinhengSql);
                        }
                    }

                }
                else
                {
                   
                    string update_item = string.Format("update  [AIS20171206082230].[dbo].[t_Item] set [isSync]=2 where [danwei]='{0}' ;", FItemID);
                    NQueryMSSql(update_item, SQL_conn);
                    Uplog(FNumber + "已存在");
                    Write.WriteLog(FNumber + "已存在");

                }
                 

            }
            falg = true;
            return falg;
           
        }

        private bool existInPlm(string fNumber)
        {
            bool flg = false;
            string sql = " SELECT 1 FROM `material` WHERE  materialCode='"+ fNumber + "'";
            DataTable dt = EponMySql_cnn.GetDataTable(sql);
             
            if (dt.Rows.Count > 0)
            {
                flg=true;

            }
            return flg;
        }

        private void timer2_Tick(object sender, EventArgs e)
        {
            xinhengchushihua();
            logint3 += 1;
            Uplog("循环次数" + logint3);
            logint2 = ProcessName("MySqlSyncSqlServer");
            Uplog("当前程序内存占用：" + logint2);
            Write.WriteLog("循环次数:" + logint3 + ";  " + "当前程序内存占用：" + logint2 + "MB", "SQLDataSync");
            if (logint2 > 200)
            {
                Application.Exit();
                System.Diagnostics.Process.Start(System.Reflection.Assembly.GetExecutingAssembly().Location);
            }
        }

        private void btnOpenDialog_Click(object sender, EventArgs e)
        {
            
        }

        private void 加载ExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {

            try
            {
                OpenFileDialog fileDialog = new OpenFileDialog();
                fileDialog.Multiselect = false;
                fileDialog.Filter = "Excel 97-2003 workbook|*.xls|Excel 2010 workbook|*.xlsx";
                fileDialog.FilterIndex = 2;
                if (fileDialog.ShowDialog() == DialogResult.OK)
                {
                    if (fileDialog.FileName != "")
                    {
                        string filepath = fileDialog.FileName;
                        DataTable dtTemp = ReadExcelToDataSet1(filepath).Tables[0];
                        gridControl1.DataSource = dtTemp; //ds;

                    }
                }

            }
            catch (Exception ex )
            {
                Console.WriteLine(  ex );

            }
        }
        //3，利用ExcelDataReader 读取excel文件
        public DataSet ReadExcelToDataSet1(string fileNmaePath)
        {
            FileStream stream = null;
            IExcelDataReader excelReader = null;
            DataSet dataSet = null;
            try
            {
                //stream = File.Open(fileNmaePath, FileMode.Open, FileAccess.Read);
                stream = new FileStream(fileNmaePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            }
            catch
            {
                return null;
            }
            string extension = Path.GetExtension(fileNmaePath);

            if (extension.ToUpper() == ".XLS")
            {
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            }
            else if (extension.ToUpper() == ".XLSX")
            {
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            }
            else
            {
                MessageBox.Show("格式错误");
                return null;

            }
            //dataSet = excelReader.AsDataSet();//第一行当作数据读取
            dataSet = excelReader.AsDataSet(new ExcelDataSetConfiguration()
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            });//第一行当作列名读取
            excelReader.Close();
            return dataSet;
        }

        private void gridView1_CustomDrawRowIndicator(object sender, DevExpress.XtraGrid.Views.Grid.RowIndicatorCustomDrawEventArgs e)
        {
            e.Appearance.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far;
            if (e.Info.IsRowIndicator)
            {
                if (e.RowHandle >= 0)
                {
                    e.Info.DisplayText = (e.RowHandle + 1).ToString();
                }
                else if (e.RowHandle < 0 && e.RowHandle > -1000)
                {
                    e.Info.Appearance.BackColor = System.Drawing.Color.AntiqueWhite;
                    e.Info.DisplayText = "G" + e.RowHandle.ToString();
                }
            } 
            gridView1.IndicatorWidth = 50;  //设置行号列宽度为50，不然行号多时显示不完全 
        }

        private void 发送到PLMToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                this.gridView1.CloseEditor();
                this.gridView1.UpdateCurrentRow(); 
                Control.CheckForIllegalCrossThreadCalls = false;
           
             
                int i = 1;
                int j = 1;
                #region //取任务 
                #endregion
                if (gridView1.DataRowCount > 0)
                {
                    this.splashScreenManager1.ShowWaitForm();

                    for (int kk = 0; kk < gridView1.DataRowCount; kk++)
                    { 
                        try
                        {
                            DataRow row = gridView1.GetDataRow(kk);
                            this.splashScreenManager1.SetWaitFormCaption(kk.ToString() + "/ " + gridView1.DataRowCount);
                            this.splashScreenManager1.SetWaitFormDescription($"正在处理{row["materialCode"]}");
                            Stopwatch sw = new Stopwatch();
                            sw.Start();
                             string code=   row["materialCode"].ToString();
                            comOperate comOperate = ExcuteInsert(row);
                            if (comOperate.status == operateStatus.Success)
                            {
                                gridView1.GetDataRow(kk).SetField("result", "更新成功");
                                this.splashScreenManager1.SetWaitFormDescription($"更新成功");
                            }
                            if (comOperate.status == operateStatus.Fail)
                            {
                                gridView1.GetDataRow(kk).SetField("result", "编码已存在");
                                this.splashScreenManager1.SetWaitFormDescription($"编码已存在");
                            }
                            if (comOperate.status == operateStatus.Exception)
                            {
                                gridView1.GetDataRow(kk).SetField("result", "更新失败");
                                this.splashScreenManager1.SetWaitFormDescription($"更新异常");
                            }
                            sw.Stop();

                            


                            Thread.Sleep(100); 
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message); 
                        }
                    }
                    this.splashScreenManager1.CloseWaitForm();

                }



            }
            catch (Exception ex )
            { 
                MessageBox.Show( ex.Message,"异常");
            } 
        }

        private comOperate ExcuteInsert( DataRow row )
        {
            comOperate comOperate = new comOperate();
            // 物料编码
            string materialCode = row["materialCode"].ToString().Trim();
            // 名称
            string name = row["name"].ToString().Trim();
            string figureNum = row["figureNum"].ToString().Trim();

            string unitMeasurement = row["unitMeasurement"].ToString().Trim();

             
            if (!existInPlm(materialCode))
            {
                if (materialCode != null)
                {
                     SonwID sf = new  SonwID(0);

                    string id = (sf.nextId()).ToString();
                    //组合SQL语句，将数据插入到编码分类下，category=1
                    string insertXinhengSql = string.Format("INSERT INTO `plmpj`.`material`" +
                            "(`id`,`codeRuleID`, `category`,  `materialCode`,          `name`, `figureNum`, `departmentId`,    `status`, unitMeasurement) " +
                            $"VALUES ('{id}','20251104200644','1', '{materialCode}',  '{name}', '{figureNum}',  '{departmentid}', '3',  '{unitMeasurement}');" );

                    if (NQueryMySql(insertXinhengSql, My_conn))
                    {
                        comOperate.status = operateStatus.Success;
                        comOperate.data = "OK";
                    }
                    else
                    {
                        comOperate.status = operateStatus.Exception;
                        //string update_item = string.Format("update  [AIS20171206082230].[dbo].[t_Item] set [isSync]=3 where [danwei]='{0}' ;", unitMeasurement);
                        //NQueryMSSql(update_item, SQL_conn);
                        string str1 = "insertXH_PLM_Material！-->" + materialCode + "-->" + figureNum + "-->" +   "-->" + unitMeasurement + "-->" + "失败！";
                        comOperate.data = "Exception";
                    }
                }

            }
            else
            {

               // string update_item = string.Format("update  [AIS20171206082230].[dbo].[t_Item] set [isSync]=2 where [danwei]='{0}' ;", unitMeasurement);
              
                comOperate.status = operateStatus.Fail;
                comOperate.data =  "ERR";
                 

            }

            return  comOperate;
        }
        SQLiteHelper2 sQLiteHelper2 = new SQLiteHelper2();
        //xinheng bumen ---hauweiyun202890544441466880
        
        private void 保存到数据库ToolStripMenuItem_Click(object sender, EventArgs e)
        {
           string app= Application.StartupPath;
            sQLiteHelper2.dbPath = Path.Combine(app, "xh.db");
            try
            {
                this.gridView1.CloseEditor();
                this.gridView1.UpdateCurrentRow(); 
                Control.CheckForIllegalCrossThreadCalls = false;
           
                
                int i = 1;
                int j = 1; 
                if (gridView1.DataRowCount > 0)
                {
                    this.splashScreenManager1.ShowWaitForm();
                    for (int kk = 0; kk < gridView1.DataRowCount; kk++)
                    {
                        try
                        {
                            Stopwatch sw = new Stopwatch();
                            sw.Start();
                            DataRow row = gridView1.GetDataRow(kk);
                            comOperate comOperate = saveData(row); 
                            sw.Stop();
                            Thread.Sleep(100); 
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message); 
                        }
                    }
                    this.splashScreenManager1.CloseWaitForm();
                } 
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "异常");
            }

        }

        private comOperate saveData(DataRow row)
        {
            comOperate comOperate = new comOperate();
            comOperate.status = operateStatus.Fail;
            string insertXinhengSql = string.Format($"INSERT INTO \"main\".\"E10\"(\"ITEM_CODE\", \"ITEM_NAME\", \"ITEM_SPECIFICATION\", \"ITEM_DESC\", \"STOCK_UNIT_ID_CODE\", \"result\") VALUES " +
                $"('{row["ITEM_CODE"]}', '{row["ITEM_NAME"]}', '{row["ITEM_SPECIFICATION"]}', '{row["ITEM_DESC"]}', '{row["STOCK_UNIT_ID_CODE"]}', '{row["result"]}');");
            try
            {
                sQLiteHelper2.executeNonQuery(insertXinhengSql);
                comOperate.status = operateStatus.Success; 
            }
            catch (Exception ex )
            {
                Console.WriteLine(ex); 
            }
            return comOperate;  


        }

        private void 导出ExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            ExportToExcel(gridView1);
        }
        public void ExportToExcel(GridView gridView1)
        {
            using (SaveFileDialog fd = new SaveFileDialog())
            {
                fd.Title = "导出Excel文件";
                fd.RestoreDirectory = true;
                fd.Filter = "Excel文件|*.xls(*.xlsx)";
                fd.FilterIndex = 1;
                fd.FileName ="导出编码清单"+ DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                if (fd.ShowDialog() == DialogResult.OK)
                {
                    //bool hasFindType = false;
                    //GridView gridView1 = obj as DevExpress.XtraGrid.Views.Grid.GridView;
                    if (gridView1 != null)
                    {
                        // hasFindType = true;
                        //string path = fbd.SelectedPath + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                        gridView1.ExportToXls(fd.FileName);
                    }
                }
            }
        }
        DataTable plm_dt = new DataTable();
        DataTable  erp_dt = new DataTable();
        private void 加载数据库数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (id_cnn == 0)
            {

                //加载erp数据
                erp_dt = sqlnet_erp.ExecuteQueryDT("select top 2000 * from v_bas_inventory ;");
                DataTable erp_dt2 = erp_dt.Clone();
                erp_dt2.Rows.Clear();
                //加载plm数据
                plm_dt = EponMySql_cnn.GetDataTable("SELECT * FROM `plmpj`.`material` limit 0,1000 ; ");
                List<DataRow> drs = new List<DataRow>();
                foreach (DataRow item in erp_dt.Rows)
                {
                    string cInvCode = item["InvCode"].ToString();
                    if (!isExistInPlmDatatable(cInvCode))
                    {
                        erp_dt2.ImportRow(item);
                    }
                    else
                    {
                        Console.WriteLine(cInvCode + "已存在！！"); 
                        Uplog(cInvCode + "已存在！！");
                    }
                }
                string cfgStr = JsonConvert.SerializeObject(erp_dt2);
                using (FileStream fs = new FileStream("D:\\data.json", FileMode.Create, FileAccess.Write, FileShare.Write))
                {
                    //将字符串字符串转成byte数组  
                    byte[] byteFile = Encoding.UTF8.GetBytes(cfgStr);
                    //参数：要写到文件的数据数组，从第几个开始写，一共写多少个  
                    fs.Write(byteFile, 0, byteFile.Length);
                }
                insertMaterialCode_PLM(erp_dt2);
            }

            if (id_cnn == 5)
            {

                //加载erp数据
                erp_dt = sqlnet_erp.ExecuteQueryDT("SELECT [isSync]\r\n" +
                    "      ,[FMATERIALID]\r\n" +
                    "      ,[FNUMBER]\r\n" +
                    "      ,[FNAME]\r\n" +
                    "      ,[F_UGOZ_TEXT]\r\n" +
                    "      ,[FSPECIFICATION]\r\n" +
                    "      ,[FUnit]\r\n" +
                    "      ,[Fnumber_color]\r\n" +
                    "      ,[F_UGOZ_ASSISTANT3]\r\n" +
                    "      ,[F_UGOZ_TEXT9]\r\n" +
                    "      ,[FCREATEDATE]\r\n" +
                    "      ,[FMODIFYDATE]\r\n" +
                    "  FROM [AIS20211223170448].[dbo].[View_PLM_ITEM] WHERE isSync=0 ;");
                DataTable erp_dt2 = erp_dt.Clone();
                erp_dt2.Rows.Clear();
                //加载plm数据
                plm_dt = EponMySql_cnn.GetDataTable("SELECT * FROM `plmpj`.`material` limit 0,1000 ; ");
                List<DataRow> drs = new List<DataRow>();
                foreach (DataRow item in erp_dt.Rows)
                {
                    string cInvCode = item["InvCode"].ToString();
                    if (!isExistInPlmDatatable(cInvCode))
                    {
                        erp_dt2.ImportRow(item);
                    }
                    else
                    {
                        Console.WriteLine(cInvCode + "已存在！！");
                        Uplog(cInvCode + "已存在！！");
                    }
                }
                string cfgStr = JsonConvert.SerializeObject(erp_dt2);
                using (FileStream fs = new FileStream("D:\\data.json", FileMode.Create, FileAccess.Write, FileShare.Write))
                {
                    //将字符串字符串转成byte数组  
                    byte[] byteFile = Encoding.UTF8.GetBytes(cfgStr);
                    //参数：要写到文件的数据数组，从第几个开始写，一共写多少个  
                    fs.Write(byteFile, 0, byteFile.Length);
                }
                insertMaterialCode_PLM(erp_dt2);
            }
        }

        private void sendCodeToPlm(DataTable erp_dt2)
        {
            foreach (DataRow item in erp_dt2.Rows)
            {
                string cInvCode = item["cInvCode"].ToString();
                string cInvName = item["cInvName"].ToString();
                string cInvStd = item["cInvStd"].ToString();
                string cInvDefine1 = item["cInvDefine1"].ToString();
                string cInvDefine2 = item["cInvDefine2"].ToString();
                string cInvDefine3 = item["cInvDefine3"].ToString();
                string cInvDefine4 = item["cInvDefine4"].ToString();
                string cInvDefine5 = item["cInvDefine5"].ToString();
                string cInvDefine6 = item["cInvDefine6"].ToString();
                string cInvDefine7 = item["cInvDefine7"].ToString();
                string cInvDefine8 = item["cInvDefine8"].ToString();
                string cInvDefine9 = item["cInvDefine9"].ToString();
                string cInvDefine10 = item["cInvDefine10"].ToString();
                string cInvDefine11 = item["cInvDefine11"].ToString();
                string cInvDefine12 = item["cInvDefine12"].ToString();
            }  
        }

        public bool insertMaterialCode_PLM(DataTable table )
        {
            bool falg = false;
            Uplog("insertXH_PLM_Material执行完毕！-->");

            for (int i = 0; i < table.Rows.Count; i++)
            {
                // 物料编码
                string FNumber = table.Rows[i]["InvCode"].ToString();
                // 名称
                string Fname = table.Rows[i]["InvName"].ToString(); 
                // 规格
                string FModel = table.Rows[i]["InvStd"].ToString();
                string danwei = table.Rows[i]["ComUnitName"].ToString();

                Console.WriteLine(FNumber + "正在处理...");
                Uplog(FNumber + "正在处理...");
                if (FNumber != null)
                {
                    SonwID sf = new SonwID(0);

                    string id = (sf.nextId()).ToString();
                    //组合SQL语句，将数据插入到编码分类下，category=1
                    string insertXinhengSql = string.Format("INSERT INTO `plmpj`.`material`" +
                            "(`id`,`codeRuleID`, `category`,  `materialCode`, `name`, `figureNum`, `departmentId`, `specification`, `status`) " +
                            "VALUES ('{0}', '202511062220','1', '{1}',         '{2}',   '{4}', '744495185206452224', '{3}',           '3');", id, FNumber, Fname, null, FModel);

                    if (NQueryMySql(insertXinhengSql, My_conn))
                    {
                     
                        string str2 = "insertXH_PLM_Material！-->" + FNumber + "-->" + Fname + "-->" + FModel + "-->" +  "-->" + "成功！";
                        Uplog(str2);
                        Write.WriteLog(str2);
                    }
                    else
                    {
                        string str1 = "insertXH_PLM_Material！-->" + FNumber + "-->" + Fname + "-->" + FModel + "-->" +   "-->" + "失败！";
                        Uplog(str1);
                        Write.WriteLog(str1);
                    }
                } 
            }
            falg = true;
            return falg;
        }

        public bool isExistInPlmDatatable(string cInvCode)
        {
           
            bool containsString = plm_dt.AsEnumerable().Any(row => row["materialCode"].ToString()==(cInvCode.Trim()));
            return containsString;


        } 
        private void 加载本地数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
           
            string cfgStr = File.ReadAllText("D:\\data.json");
           DataTable dt4 =  JsonConvert.DeserializeObject<DataTable>(cfgStr);

            insertMaterialCode_PLM(dt4);
        }

        private void 比对数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //时纬项目专用
            if (id_cnn == 0)
            { 
                //加载erp数据
                erp_dt = sqlnet_erp.ExecuteQueryDT(" select * from v_bas_inventory_temp ;"); 
                //加载plm数据
                //plm_dt = EponMySql_cnn.GetDataTable("SELECT * FROM `plmpj`.`material` ; ");
                List<DataRow> drs = new List<DataRow>();
                foreach (DataRow item in erp_dt.Rows)
                {
                    string str=string.Empty;
                    string cInvCode = item["InvCode"].ToString();
                    //if (isExistInPlmDatatable(cInvCode))
                    //{
                    //    str = $"  update  v_bas_inventory_temp  set InvDefine1 ='YES'  where   InvCode='{cInvCode}'";
                    //}
                    //else
                    //{
                   str = $"  update  v_bas_inventory_temp  set InvDefine1 ='time' where   InvCode='{cInvCode}'";
                    //}
                   sqlnet_erp.ExecuteQuery(str); 
                    EponMySql_cnn.ExecuteNonQuery($"UPDATE `plmpj`.`material` SET `erpStatus` = 2 ,erpSendTime='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}' WHERE category=1 and  `materialCode` = '{cInvCode}'");
                } 
            } 
        }

        private void 时纬Excel更新PLMToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                this.gridView1.CloseEditor();
                this.gridView1.UpdateCurrentRow();
                Control.CheckForIllegalCrossThreadCalls = false;
                string cmdSql = string.Empty;

                int i = 1;
                int j = 1;
                #region //取任务 
                #endregion
                if (gridView1.DataRowCount > 0)
                {
                    this.splashScreenManager1.ShowWaitForm();

                    for (int kk = 0; kk < gridView1.DataRowCount; kk++)
                    {
                        try
                        {
                            DataRow row = gridView1.GetDataRow(kk);
                            this.splashScreenManager1.SetWaitFormCaption(kk.ToString() + "/ " + gridView1.DataRowCount);
                            this.splashScreenManager1.SetWaitFormDescription($"正在处理{row["materialCode"]}");
                            Stopwatch sw = new Stopwatch();
                            sw.Start();
                            string code = row["materialCode"].ToString();
                            string materialCode = row["materialCode"].ToString().Trim();
                            // 名称
                            string name = row["name"].ToString().Trim();
                            string figureNum = row["figureNum"].ToString().Trim();

                            //cmdSql += $"update `plmpj`.`material` set name ='{name}',figureNum='{figureNum}' where materialCode='{materialCode}' and category=1; ";

                            //if (EponMySql_cnn.ExecuteNonQuery(cmdSql) > 0)
                            //{
                            //    comOperate.status = operateStatus.Success;
                            //    comOperate.data = "OK";
                            //}
                            //else
                            //{
                            //    comOperate.status = operateStatus.Exception;
                            //    comOperate.data = "Exception";
                            //}

                            comOperate comOperate = ExcuteUpdate(row);
                            if (comOperate.status == operateStatus.Success)
                            {
                                gridView1.GetDataRow(kk).SetField("result", "更新成功");
                                this.splashScreenManager1.SetWaitFormDescription($"更新成功");
                            }
                            if (comOperate.status == operateStatus.Fail)
                            {
                                gridView1.GetDataRow(kk).SetField("result", "编码已存在");
                                this.splashScreenManager1.SetWaitFormDescription($"编码已存在");
                            }
                            if (comOperate.status == operateStatus.Exception)
                            {
                                gridView1.GetDataRow(kk).SetField("result", "更新失败");
                                this.splashScreenManager1.SetWaitFormDescription($"更新异常");
                            }
                            sw.Stop();




                            Thread.Sleep(100);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                    }
                    this.splashScreenManager1.CloseWaitForm();
                    //File.WriteAllText("./sqlcmd.sql",cmdSql);
                }



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "异常");
            }
        }

        private comOperate ExcuteUpdate(DataRow row)
        {
            comOperate comOperate = new comOperate();
            // 物料编码
            string materialCode = row["materialCode"].ToString().Trim();
            // 名称
            string name = row["name"].ToString().Trim();
            string figureNum = row["figureNum"].ToString().Trim();
            string cinvstd = row["cinvstd"].ToString().Trim();

            if (existInPlm(materialCode))
            {
                if (materialCode != null)
                { 
                    string cmdSql = $"update `plmpj`.`material` set name ='{name}',figureNum='{figureNum}',cinvstd='{cinvstd}' where materialCode='{materialCode}' and category=1; ";
                    Console.WriteLine(materialCode+"_"+figureNum + "_"+name + "_" + cinvstd);
                    if (EponMySql_cnn.ExecuteNonQuery(cmdSql)>0)
                    {
                        comOperate.status = operateStatus.Success;
                        comOperate.data = "OK";
                    }
                    else
                    {
                        comOperate.status = operateStatus.Exception;
                        comOperate.data = "Exception";
                    }
                }

            }
            else
            {

                // string update_item = string.Format("update  [AIS20171206082230].[dbo].[t_Item] set [isSync]=2 where [danwei]='{0}' ;", unitMeasurement);

                comOperate.status = operateStatus.Fail;
                comOperate.data = "ERR";


            }

            return comOperate;
        }

        private void 设置禁用ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                this.gridView1.CloseEditor();
                this.gridView1.UpdateCurrentRow();
                Control.CheckForIllegalCrossThreadCalls = false;
                string cmdSql = string.Empty;

                int i = 1;
                int j = 1;
                #region //取任务 
                #endregion
                if (gridView1.DataRowCount > 0)
                {
                    this.splashScreenManager1.ShowWaitForm();

                    for (int kk = 0; kk < gridView1.DataRowCount; kk++)
                    {
                        try
                        {
                            DataRow row = gridView1.GetDataRow(kk);
                            this.splashScreenManager1.SetWaitFormCaption(kk.ToString() + "/ " + gridView1.DataRowCount);
                            this.splashScreenManager1.SetWaitFormDescription($"正在处理{row["materialCode"]}");
                            Stopwatch sw = new Stopwatch();
                            sw.Start();
                            
                            string materialCode = row["materialCode"].ToString().Trim();
                            // 名称
                            string name = row["name"].ToString().Trim();
                            string figureNum = row["figureNum"].ToString().Trim();
                            Console.WriteLine(materialCode+"_"+name+"_"+figureNum);
                            comOperate comOperate = ExcuteDisUse(row);
                            if (comOperate.status == operateStatus.Success)
                            {
                                gridView1.GetDataRow(kk).SetField("result", "更新成功");
                                this.splashScreenManager1.SetWaitFormDescription($"更新成功");
                            }
                            if (comOperate.status == operateStatus.Fail)
                            {
                                gridView1.GetDataRow(kk).SetField("result", "编码已存在");
                                this.splashScreenManager1.SetWaitFormDescription($"编码已存在");
                            }
                            if (comOperate.status == operateStatus.Exception)
                            {
                                gridView1.GetDataRow(kk).SetField("result", "更新失败");
                                this.splashScreenManager1.SetWaitFormDescription($"更新异常");
                            }
                            sw.Stop(); 
                            Thread.Sleep(100);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                    }
                    this.splashScreenManager1.CloseWaitForm();
                    //File.WriteAllText("./sqlcmd.sql",cmdSql);
                }



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "异常");
            }
        }

        private comOperate ExcuteDisUse(DataRow row)
        {
            comOperate comOperate = new comOperate();
            // 物料编码
            string materialCode = row["materialCode"].ToString().Trim();
            // 名称
            string name = row["name"].ToString().Trim();
            string figureNum = row["figureNum"].ToString().Trim();
            string cinvstd = row["cinvstd"].ToString().Trim();

            if (existInPlm(materialCode))
            {
                if (materialCode != null)
                {
                    string cmdSql = $"update `plmpj`.`material` set  status=4  where materialCode='{materialCode}' and category=1; ";
                    Console.WriteLine("禁用"+materialCode + "_" + figureNum + "_"  );
                    if (EponMySql_cnn.ExecuteNonQuery(cmdSql) > 0)
                    {
                        comOperate.status = operateStatus.Success;
                        comOperate.data = "OK";
                    }
                    else
                    {
                        comOperate.status = operateStatus.Exception;
                        comOperate.data = "Exception";
                    }
                }

            }
            else
            {

                // string update_item = string.Format("update  [AIS20171206082230].[dbo].[t_Item] set [isSync]=2 where [danwei]='{0}' ;", unitMeasurement);

                comOperate.status = operateStatus.Fail;
                comOperate.data = "ERR";


            }

            return comOperate;
        }
    }
    public class Sqlnet
    {
        public SqlConnection conn = new SqlConnection();
        public string server = "";
        public string database = "";
        public string uid = "";
        public string password = "";


        public Sqlnet(string ip, string Uid, string Pwd, string DataBase)
        {
            this.server = ip;

            this.uid = Uid;

            this.password = Pwd;
            this.database = DataBase;
            GetConnection();
        }


        public string Ip { get => server; set => server = value; }
        public string Uid1 { get => this.uid; set => this.uid = value; }
        public string Pwd1 { get => password; set => password = value; }
        public string DataBase1 { get => database; set => database = value; }

        public static string Base64Decode(string str)
        {
            byte[] barray = Convert.FromBase64String(str);
            return Encoding.Default.GetString(barray);
        }

        public static string Base64Encode(string str)
        {
            return Convert.ToBase64String(Encoding.Default.GetBytes(str));
        }


        public bool ConnectTest()
        {
            string message = string.Empty;
            return ConnectTest(this.server, this.database, this.uid, this.password, ref message);
        }
        public bool ConnectTest(string server, string database, string uid, string password, ref string message)
        {
            bool flag = true;
            try
            {
                string str = $"server={server};database={database};uid={uid};password={password};Connect Timeout=3600;Pooling=False";
                this.conn = new SqlConnection();
                if (this.conn.State != ConnectionState.Open)
                {
                    this.conn.ConnectionString = str;
                    this.conn.Open();
                    message = "连接成功";
                }
            }
            catch (Exception exception)
            {
                flag = false;
                message = "连接失败！原因：" + exception.Message;
            }
            return flag;
        }

        public void ExecuteNonQuery(string sql)
        {
            this.GetConnection();
            new SqlCommand
            {
                Connection = this.conn,
                CommandType = CommandType.Text,
                CommandText = sql,
                CommandTimeout = 0xe10
            }.ExecuteNonQuery();
            if (this.conn.State != ConnectionState.Closed)
            {
                this.conn.Close();
                this.conn.Dispose();
            }
        }

        public DataTable ExecuteQuery(string sql)
        {
            this.GetConnection();
            DataTable dataTable = new DataTable();
            SqlCommand selectCommand = new SqlCommand
            {
                Connection = this.conn,
                CommandType = CommandType.Text,
                CommandText = sql,
                CommandTimeout = 0xe10
            };
            if (!string.IsNullOrEmpty(sql))
            {
                new SqlDataAdapter(selectCommand).Fill(dataTable);
            }
            if (this.conn.State != ConnectionState.Closed)
            {
                this.conn.Close();
                this.conn.Dispose();
            }
            return dataTable;
        }

        public DataSet ExecuteQueryDataSet(string sql)
        {
            this.GetConnection();
            DataSet dataSet = new DataSet();
            SqlCommand selectCommand = new SqlCommand
            {
                Connection = this.conn,
                CommandType = CommandType.Text,
                CommandText = sql,
                CommandTimeout = 0xe10
            };
            if (!string.IsNullOrEmpty(sql))
            {
                new SqlDataAdapter(selectCommand).Fill(dataSet);
            }
            if (this.conn.State != ConnectionState.Closed)
            {
                this.conn.Close();
                this.conn.Dispose();
            }
            return dataSet;
        }

        public string ExecuteScalar(string sql)
        {
            string str = "";
            this.GetConnection();
            object obj2 = new SqlCommand
            {
                Connection = this.conn,
                CommandType = CommandType.Text,
                CommandText = sql,
                CommandTimeout = 0xe10
            }.ExecuteScalar();
            if (obj2 == null)
            {
                str = "";
            }
            else
            {
                str = obj2.ToString();
            }
            if (this.conn.State != ConnectionState.Closed)
            {
                this.conn.Close();
                this.conn.Dispose();
            }

            return str;
        }

        public void GetConnection()
        {
            try
            {
                string str = $"server={this.server};database={this.database};uid={this.uid};password={this.password};Connect Timeout=120";
                this.conn = new SqlConnection();
                if (this.conn.State != ConnectionState.Open)
                {
                    this.conn.ConnectionString = str;
                    this.conn.Open();
                }
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>

        public string SWExecuteNonQuery(string sql)
        {
            string message = "";
            this.GetConnection();
            SqlCommand command = new SqlCommand
            {
                Connection = this.conn,
                CommandType = CommandType.Text,
                CommandText = sql,
                CommandTimeout = 0xe10
            };
            SqlTransaction transaction = this.conn.BeginTransaction();
            command.Transaction = transaction;
            try
            {
                command.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (Exception exception)
            {
                transaction.Rollback();
                message = exception.Message;
            }
            finally
            {
                if (this.conn.State != ConnectionState.Closed)
                {
                    this.conn.Close();
                    this.conn.Dispose();
                }
            }
            return message;
        }

        /// <summary>
        /// 过滤sql关键字
        /// </summary>
        /// <param name="inputString"></param>
        /// <returns>替换当前字符串中sql的关键字为#</returns>
        public static string ConvertSql(string inputString)
        {
            inputString = inputString.Trim();
            inputString = inputString.Replace("'", "''");
            inputString = inputString.Replace(";--", "#");
            inputString = inputString.Replace("=", "#");
            inputString = inputString.Replace("or", "#");
            inputString = inputString.Replace("OR", "#");
            inputString = inputString.Replace("and", "#");
            inputString = inputString.Replace("AND", "#");
            inputString = inputString.Replace("exec", "#");
            inputString = inputString.Replace("execute", "#");
            inputString = inputString.Replace("insert", "#");
            inputString = inputString.Replace("select", "#");
            inputString = inputString.Replace("delete", "#");
            inputString = inputString.Replace("update", "#");
            inputString = inputString.Replace("alter", "#");
            inputString = inputString.Replace("create", "#");
            inputString = inputString.Replace("drop", "#");
            inputString = inputString.Replace("*", "#");
            inputString = inputString.Replace("chr", "#");
            inputString = inputString.Replace("char", "#");
            inputString = inputString.Replace("asc", "#");
            inputString = inputString.Replace("char", "#");
            inputString = inputString.Replace("mid", "#");
            inputString = inputString.Replace("substring", "#");
            inputString = inputString.Replace("master", "#");
            inputString = inputString.Replace("truncate", "#");
            inputString = inputString.Replace("declare", "#");
            inputString = inputString.Replace("xp_cmdshell", "#");
            inputString = inputString.Replace("restore", "#");
            inputString = inputString.Replace("backup", "#");
            return inputString;
        }


        public DataTable ExecuteQueryDT(string sqlText)
        {
            this.GetConnection();
            using (SqlCommand command = new SqlCommand(sqlText, this.conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet dataSet = new DataSet();
                try
                {
                    adapter.Fill(dataSet);
                }
                catch (Exception exception)
                {
                    throw exception;
                }
                finally
                {
                    if (this.conn.State != ConnectionState.Closed)
                    {
                        this.conn.Close();
                        this.conn.Dispose();
                    }
                }
                DataTable table = dataSet.Tables[0];
                dataSet.Tables.Remove(dataSet.Tables[0]);
                return table;
            }
        }



    }

}
